package com.cm367.teamc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.faces.bean.ApplicationScoped;
import javax.faces.bean.ManagedBean;

import com.cm367.teamc.bean.Holiday;
import com.cm367.teamc.util.DateUtil;

@ManagedBean
@ApplicationScoped
public class PublicHolidayDAO {

	public boolean addPubilcHoliday(String holidayName, Date date)
			throws SQLException {
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("insert into publicholiday values(?,?)");
		ps.setString(1, holidayName);
		ps.setString(2, DateUtil.getStringDate(date));
		ps.execute();
		ps.close();
		return true;
	}

	public boolean deletePublicHoliday(String date) throws SQLException {
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("delete from publicholiday where date=?");
		ps.setString(1, date);
		boolean flag = ps.execute();
		ps.close();
		return flag;
	}

	public List<String> getPublicHoliday() throws SQLException {
		List<String> holidays = new ArrayList<String>();
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("select * from publicholiday");

		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			holidays.add(rs.getString("date"));
		}
		rs.close();
		ps.close();
		return holidays;

	}

	public boolean existHoliday(Date date) throws SQLException {
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("select count(*) from publicholiday where date=?");
		ps.setString(1, DateUtil.getStringDate(date));
		ResultSet rs = ps.executeQuery();
		rs.next();
		int count = rs.getInt(1);
		rs.close();
		ps.close();
		if (count == 0) {
			return false;
		}
		return true;
	}

	public boolean updateHoliday(String name, Date date) throws SQLException {
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("update publicholiday set holidayname=? where date=?");
		ps.setString(1, name);
		ps.setString(2, DateUtil.getStringDate(date));
		ps.execute();
		ps.close();
		return true;
	}

	public List<Holiday> findAllHoliday() throws SQLException {
		List<Holiday> holidays = new ArrayList<Holiday>();
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("select * from publicholiday order by date");
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			String name = rs.getString("holidayname");
			String date = rs.getString("date");
			holidays.add(new Holiday(name, date));
		}
		rs.close();
		ps.close();
		return holidays;
	}

	public List<String> findHolidayDates(int year) throws SQLException {
		List<String> holidays = new ArrayList<String>();
		Connection conn = BaseDAO.getConnection();
		PreparedStatement ps = conn
				.prepareStatement("select * from publicholiday where date<=? and date>=? order by date");
		ps.setString(1, year + "-12-31");
		ps.setString(2, year + "-01-01");
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			String date = rs.getString("date");
			holidays.add(date);
		}
		rs.close();
		ps.close();
		return holidays;
	}
}
